/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.vng.zing.zprofilemiddleware.model;

import com.mysql.jdbc.Statement;
import com.vng.zing.calc.thrift.GENDER;
import com.vng.zing.calc.thrift.Profile;
import com.vng.zing.calc.thrift.ProfilePage;
import com.vng.zing.calc.thrift.ROLE;
import com.vng.zing.calc.thrift.SOURCE;
import com.vng.zing.calc.thrift.STATUS;
import com.vng.zing.logger.ZLogger;
import com.vng.zing.zprofilemiddleware.common.Encription;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import org.apache.log4j.Logger;
import org.apache.thrift.TException;

/**
 * @Note: Class model xử lý business chính cho mỗi loại handler , được thiết kế
 * theo pattern Singleton Object, thiết kế kiểu này cho phép các Model object
 * truy xuất được lẫn nhau (cùng package nên truy xuất được các thuộc tính
 * protected của nhau), có thể bổ sung thiết kế bằng cách tạo ra 1 TBaseModel xử
 * lý các hàm tiện ích chung, các Biz Model khác thừa kế từ đó
 *
 * @author namnq
 */
public class ZProfileModel {

    private static final Logger _Logger = ZLogger.getLogger(ZProfileModel.class);
    public static final ZProfileModel Instance = new ZProfileModel();
    private int profileCount = 0;

    private ZProfileModel() {
        setProfileCount();
    }

    public Map<Short, String> getAllCity() {
        Connection profileMySqlConnection = null;
        try {
            profileMySqlConnection = ZProfileMySqlConnection.getInstance().getDbConnection();
            String sql = "SELECT * FROM city";
            PreparedStatement preparedStatement = profileMySqlConnection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            Map<Short, String> a = mapToCity(resultSet);
            return a;
        } catch (SQLException ex) {
            _Logger.error("get getAllCity  " + ex.toString());
        } finally {
            ZProfileMySqlConnection.getInstance().releaseDbConnection(profileMySqlConnection);
        }
        return null;
    }

    private static PreparedStatement buildInsertSQL(Connection profileMySqlConnection, Profile profile) {
        try {
            String sql = "INSERT INTO profile (id, username, password, display_name, date_of_birth, gender, email, mobile_number, created_date, modified_date, modified_by, status, role, city_id,source,UID) VALUES (NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement preparedStatement = profileMySqlConnection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, profile.getUsername());
            preparedStatement.setString(2, profile.getPassword());
            preparedStatement.setString(3, profile.getDisplayName());
            preparedStatement.setInt(4, profile.getDateOfBirth());
            preparedStatement.setInt(5, profile.getGender().getValue());
            preparedStatement.setString(6, profile.getEmail());
            preparedStatement.setString(7, profile.getMobileNumber());
            preparedStatement.setInt(8, profile.getCreatedDate());
            preparedStatement.setInt(9, profile.getModifiedDate());
            preparedStatement.setString(10, profile.getModifiedBy());
            preparedStatement.setString(11, profile.getStatus().name());
            preparedStatement.setString(12, profile.getRole().name());
            preparedStatement.setInt(13, profile.getCityId());
            preparedStatement.setString(14, profile.getSource().name());
            preparedStatement.setString(15, profile.getUID());
            return preparedStatement;
//        String sql = "INSERT INTO profile (id, username, password, display_name, date_of_birth, gender, email, mobile_number, created_date, modified_date, modified_by, status, role, city_id) VALUES "
//                + "(NULL, '" + profile.getUsername()
//                + "','" + profile.getPassword()
//                + "','" + profile.getDisplayName()
//                + "'," + profile.getDateOfBirth()
//                + ",'" + String.format("%s", profile.getGender().getValue())
//                + "','" + profile.getEmail()
//                + "','" + profile.getMobileNumber()
//                + "'," + profile.getCreatedDate()
//                + " ," + profile.getModifiedDate()
//                + " ,'" + profile.getModifiedBy()
//                + "','" + profile.getStatus() + "','"
//                + profile.getRole() + "',"
//                + String.format("%s", profile.getCityId()) + ")";
        } catch (Exception ex) {
            _Logger.error("build preparedStatement for insert profile " + profile.getUsername() + "failed.");
        }
        return null;
    }

    private static PreparedStatement buildUpdateSQL(Connection profileMySqlConnection, Profile profile) {
        try {
            String sql = "UPDATE profile SET username = ?,"
                    + "password = ?,"
                    + "display_name = ?,"
                    + "date_of_birth = ?,"
                    + "gender = ?,"
                    + "email = ? ,"
                    + "mobile_number =  ? ,"
                    + "created_date = ? ,"
                    + "modified_date = ? ,"
                    + "modified_by = ? ,"
                    + "status = ? ,"
                    + "role = ? ,"
                    + "city_id = ? WHERE id = ?";

            PreparedStatement preparedStatement = profileMySqlConnection.prepareStatement(sql);
            preparedStatement.setString(1, profile.getUsername());
            preparedStatement.setString(2, profile.getPassword());
            preparedStatement.setString(3, profile.getDisplayName());
            preparedStatement.setInt(4, profile.getDateOfBirth());
            preparedStatement.setInt(5, profile.getGender().getValue());
            preparedStatement.setString(6, profile.getEmail());
            preparedStatement.setString(7, profile.getMobileNumber());
            preparedStatement.setInt(8, profile.getCreatedDate());
            preparedStatement.setInt(9, profile.getModifiedDate());
            preparedStatement.setString(10, profile.getModifiedBy());
            preparedStatement.setString(11, profile.getStatus().name());
            preparedStatement.setString(12, profile.getRole().name());
            preparedStatement.setInt(13, profile.getCityId());
            preparedStatement.setInt(14, profile.getId());
            return preparedStatement;
//        String sql = "UPDATE profile SET username = '" + profile.getUsername() + "',"
//                + "password = '" + profile.getPassword() + "',"
//                + "display_name = '" + profile.getDisplayName() + "',"
//                + "date_of_birth = " + profile.getDateOfBirth() + ","
//                + "gender = " + profile.getGender().getValue() + ","
//                + "email = '" + profile.getEmail() + "',"
//                + "mobile_number = '" + profile.getMobileNumber() + "',"
//                + "created_date = " + profile.getCreatedDate() + ","
//                + "modified_date = " + profile.getModifiedDate() + ","
//                + "modified_by = '" + profile.getModifiedBy() + "',"
//                + "status = '" + profile.getStatus() + "',"
//                + "role = '" + profile.getRole() + "',"
//                + "city_id = " + profile.getCityId() + " WHERE id =" + profile.getId();
//        return sql;
        } catch (SQLException ex) {
            _Logger.error("build preparedStatement for update profile " + profile.getUsername() + "failed.");
        }
        return null;
    }

    public Profile createProfile(Profile profile) {
        Connection profileMySqlConnection = null;
        try {
            String salt = String.format("%s%s", profile.getUsername(), profile.role.name());
            String encriptedPassword = Encription.getHash(profile.getPassword(), salt);
            if (null == encriptedPassword) {
                return null;
            }
            profile.setPassword(encriptedPassword);
            profileMySqlConnection = ZProfileMySqlConnection.getInstance().getDbConnection();
            ///String sql = buildInsertSQL(profile);
            PreparedStatement preparedStatement = buildInsertSQL(profileMySqlConnection, profile);
            int result = preparedStatement.executeUpdate();
            if (result > 0) {
                ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
                if (generatedKeys.next()) {
                    profile.setId(generatedKeys.getInt(1));
                    increaseProfileCount();
                }
                return profile;
            } else {
                return null;
            }
        } catch (SQLException ex) {
            _Logger.error("insert profile  :" + profile.toString() + " " + ex.toString());
        } finally {
            ZProfileMySqlConnection.getInstance().releaseDbConnection(profileMySqlConnection);
        }
        return null;
    }

    public Profile logIn(String username, String password) {
        Connection profileMySqlConnection = null;
        try {

            profileMySqlConnection = ZProfileMySqlConnection.getInstance().getDbConnection();
            String sql = "SELECT * FROM profile WHERE status = 'ACTIVE' AND username = '" + username + "'";
            PreparedStatement preparedStatement = profileMySqlConnection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            Profile profile = null;
            while (resultSet.next()) {
                profile = mapToProfile(resultSet);
                break;
            }
            if (null == profile) {
                return null;
            }
            String salt = String.format("%s%s", profile.getUsername(), profile.role.name());
            String encriptedPassword = Encription.getHash(password, salt);
            if (encriptedPassword.equals(profile.getPassword())) {
                return profile;
            }
            return null;
        } catch (SQLException ex) {
            _Logger.error("logIn" + username + ":" + password + " " + ex.toString());
        } finally {
            ZProfileMySqlConnection.getInstance().releaseDbConnection(profileMySqlConnection);
        }
        return null;
    }

    public Profile getProfile(int profileId) throws TException {
        Connection profileMySqlConnection = null;
        try {
            profileMySqlConnection = ZProfileMySqlConnection.getInstance().getDbConnection();
            String sql = "SELECT * FROM profile WHERE id = " + profileId;
            PreparedStatement preparedStatement = profileMySqlConnection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                _Logger.info("get profile by id  :" + profileId);
                return mapToProfile(resultSet);
            }
            return null;
        } catch (SQLException ex) {
            _Logger.error("get profile by id  :" + profileId + " " + ex.toString());
        } finally {
            ZProfileMySqlConnection.getInstance().releaseDbConnection(profileMySqlConnection);
        }
        return null;
    }

    public Profile updateProfile(Profile profile) {
        Connection profileMySqlConnection = null;
        try {
            profileMySqlConnection = ZProfileMySqlConnection.getInstance().getDbConnection();
            //String sql = buildUpdateSQL(profile);
            PreparedStatement preparedStatement = buildUpdateSQL(profileMySqlConnection, profile);
            int result = preparedStatement.executeUpdate();
            return (result > 0) ? profile : null;
        } catch (SQLException ex) {
            _Logger.error("insert profile  :" + profile.toString() + " " + ex.toString());
        } finally {
            ZProfileMySqlConnection.getInstance().releaseDbConnection(profileMySqlConnection);
        }
        return null;
    }

    public Profile changePassword(int profileId, String currentPassword, String newPassword) {
        Connection profileMySqlConnection = null;
        try {
            profileMySqlConnection = ZProfileMySqlConnection.getInstance().getDbConnection();
            String sql = "SELECT * FROM profile WHERE id = " + profileId;
            PreparedStatement preparedStatement = profileMySqlConnection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            Profile profile = null;
            while (resultSet.next()) {
                profile = mapToProfile(resultSet);
                break;
            }
            if (null == profile) {
                return null;
            }
            String salt = String.format("%s%s", profile.getUsername(), profile.role.name());
            String encriptedPassword = Encription.getHash(currentPassword, salt);
            if (encriptedPassword.equals(profile.getPassword())) {
                encriptedPassword = Encription.getHash(newPassword, profile.getUsername() + profile.role.name());
                profile.setPassword(encriptedPassword);
                //sql = buildUpdateSQL(profile);
                preparedStatement = buildUpdateSQL(profileMySqlConnection, profile);
                int result = preparedStatement.executeUpdate();
                return (result > 0) ? profile : null;
            }
            return null;
        } catch (SQLException ex) {
            _Logger.error("changePassword  :" + profileId + " " + ex.toString());
        } finally {
            ZProfileMySqlConnection.getInstance().releaseDbConnection(profileMySqlConnection);
        }
        return null;
    }

    private static Profile mapToProfile(ResultSet rs) {
        Profile profile = new Profile();
        try {
            profile.setCityId(rs.getShort("city_id"));
            profile.setCreatedDate(rs.getInt("created_date"));
            profile.setDateOfBirth(rs.getInt("date_of_birth"));
            profile.setDisplayName(rs.getString("display_name"));
            profile.setEmail(rs.getString("email"));
            profile.setGender(GENDER.findByValue(rs.getShort("gender")));
            profile.setId(rs.getInt("id"));
            profile.setMobileNumber(rs.getString("mobile_number"));
            profile.setModifiedBy(rs.getString("modified_by"));
            profile.setModifiedDate(rs.getInt("modified_date"));
            profile.setPassword(rs.getString("password"));
            profile.setRole(ROLE.valueOf(rs.getString("role")));
            profile.setStatus(STATUS.valueOf(rs.getString("status")));
            profile.setUsername(rs.getString("username"));
            profile.setSource(SOURCE.valueOf(rs.getString("source")));
            profile.setUID(rs.getString("UID"));
        } catch (SQLException ex) {
            _Logger.error("map result set to mapToProfile :" + ex.toString());
        }
        return profile;
    }

    private static Map<Short, String> mapToCity(ResultSet rs) {
        Map<Short, String> citiesMap = new HashMap<Short, String>();
        try {
            while (rs.next()) {
                citiesMap.put(rs.getShort("id"), rs.getString("name"));
            }
        } catch (Exception ex) {
            _Logger.error("map result set to mapToCity :" + ex.toString());
        }
        return citiesMap;
    }

    public boolean validUsername(String username) throws TException {
        Connection profileMySqlConnection = null;
        try {
            profileMySqlConnection = ZProfileMySqlConnection.getInstance().getDbConnection();
            String sql = "SELECT * FROM profile WHERE username = '" + username + "'";
            PreparedStatement preparedStatement = profileMySqlConnection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                return false;
            }
            return true;
        } catch (SQLException ex) {
            _Logger.error("validUsername  :" + username + " " + ex.toString());
        } finally {
            ZProfileMySqlConnection.getInstance().releaseDbConnection(profileMySqlConnection);
        }
        return true;
    }

    public ProfilePage getAllProfile(int limit, int offset) {
        Connection profileMySqlConnection = null;
        ProfilePage ret = new ProfilePage();
        Map<Integer, Profile> proMap = new HashMap<Integer, Profile>();
        ret.setProfileMap(proMap);
        try {
            profileMySqlConnection = ZProfileMySqlConnection.getInstance().getDbConnection();
            PreparedStatement preparedStatement;
            ResultSet resultSet;
//            String sql = "SELECT COUNT(*) FROM profile";
//            preparedStatement = profileMySqlConnection.prepareStatement(sql);
//            resultSet = preparedStatement.executeQuery();
//            int total = 0;
//            while (resultSet.next()) {
//                total = resultSet.getInt("COUNT(*)");
//                ret.setTotal(total);
//                break;
//            }
//            if (total < 1) {
//                return null;
//            }

            String sql = "SELECT * FROM profile ORDER BY username ASC LIMIT " + limit + "," + offset;
            preparedStatement = profileMySqlConnection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Profile profile = mapToProfile(resultSet);
                ret.getProfileMap().put(profile.getId(), profile);
            }
            ret.setTotal(profileCount);
            return ret;
        } catch (SQLException ex) {
            _Logger.error("get all profile " + ex.toString());
        } finally {
            ZProfileMySqlConnection.getInstance().releaseDbConnection(profileMySqlConnection);
        }
        return null;
    }

    public boolean changeStatus(int profileId, STATUS status) {
        Connection profileMySqlConnection = null;
        try {
            profileMySqlConnection = ZProfileMySqlConnection.getInstance().getDbConnection();
            String sql = "UPDATE profile SET status = '" + status.name() + "' WHERE id = " + profileId;
            PreparedStatement preparedStatement = profileMySqlConnection.prepareStatement(sql);
            int result = preparedStatement.executeUpdate();
            return (result > 0);
        } catch (SQLException ex) {
            _Logger.error("changeStatus " + profileId + status + ex.toString());
        } finally {
            ZProfileMySqlConnection.getInstance().releaseDbConnection(profileMySqlConnection);
        }
        return false;
    }

    private void setProfileCount() {
        Connection profileMySqlConnection = null;
        try {
            profileMySqlConnection = ZProfileMySqlConnection.getInstance().getDbConnection();
            PreparedStatement preparedStatement;
            ResultSet resultSet;
            String sql = "SELECT COUNT(*) FROM profile";
            preparedStatement = profileMySqlConnection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            int total = 0;
            while (resultSet.next()) {
                total = resultSet.getInt("COUNT(*)");
                break;
            }
            if (total < 1) {
                _Logger.error("get total profile  fail" + total);
            } else {
                profileCount = total;
                _Logger.info("cached profile count" + total);
            }

        } catch (SQLException ex) {
            _Logger.error("get count profile " + ex.toString());
        } finally {
            ZProfileMySqlConnection.getInstance().releaseDbConnection(profileMySqlConnection);
        }

    }

    private synchronized void increaseProfileCount() {
        profileCount++;
        _Logger.info("increase profile count" + profileCount);
    }

    public Profile getSocialProfile(SOURCE source, String UID) {
        Connection profileMySqlConnection = null;
        try {
            profileMySqlConnection = ZProfileMySqlConnection.getInstance().getDbConnection();
            String sql = "SELECT * FROM profile WHERE source = '" + source.name() + "' AND UID ='" + UID + "' AND status !='DEACTIVE'";
            PreparedStatement preparedStatement = profileMySqlConnection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                _Logger.info("get social profile:" + source + UID);
                return mapToProfile(resultSet);
            }
            return null;
        } catch (SQLException ex) {
            _Logger.error("get social profile:" + source + UID + ex.toString());
        } finally {
            ZProfileMySqlConnection.getInstance().releaseDbConnection(profileMySqlConnection);
        }
        return null;
    }

}
